﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraGrid.Views.BandedGrid;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_ImportOrder : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        private int stat;

        public panel_ImportOrder()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);

            this.stat = 0;
            this.yearText.Text = DateTime.Now.Year.ToString();
            //LoadDetail();
            this.gridImportOrder.BringToFront();

            this.ImportOrder.PopupMenuShowing += CustomerClerance_PopupMenuShowing;
            this.ImportOrderContext.ItemClicked += clearanceContext_ItemClicked;

        }

        void clearanceContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.ImportOrderContext.Hide();
            switch (e.ClickedItem.Name)
            {                
                case "refreshSelection":
                    LoadDetail();
                    break;
            }
        }

        void CustomerClerance_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.ImportOrderContext.Show(MousePosition);
        }

        private void LoadDetail()
        {
            if (string.IsNullOrEmpty(this.yearText.Text.Trim()))
            {
                MessageBox.Show("Please enter the year to view report");
            }
            else
            {
                DataSet sourceDataSet = new DataSet();
                SQLManagement mySQLManagement = new SQLManagement();
                mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
                string sql = "SELECT * FROM [VArringGoodsPlan2]"
                    + " WHERE SUBSTRING([MONTH], 1, 4) = '" + this.yearText.Text.Trim() + "'"
                    + " ORDER BY [SUPPLIERID], [DELIVERYDATE]";
                sourceDataSet = mySQLManagement.SQL_Query(sql);
                if (sourceDataSet != null)
                {
                    ImportOrder.Columns.Clear();
                    gridImportOrder.DataSource = null;
                    gridImportOrder.DataSource = sourceDataSet.Tables[0];
                }
                else
                {
                    MessageBox.Show("There is no data for Import Order");
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("OrderNo", typeof(string));
                    newTable.Columns.Add("PartNo", typeof(string));
                    newTable.Columns.Add("Quantity", typeof(float));
                    newTable.Columns.Add("Price", typeof(float));
                    newTable.Columns.Add("TotalCost", typeof(float));
                    newTable.Columns.Add("Currency", typeof(string));
                    newTable.Columns.Add("Invoice", typeof(bool));
                    newTable.Columns.Add("Kobai", typeof(bool));
                    newTable.Columns.Add("DeliveryDate", typeof(string));
                    newTable.Columns.Add("Month", typeof(string));
                    newTable.Columns.Add("SupplierID", typeof(string));
                    newTable.Columns.Add("SupplierName", typeof(string));
                    newTable.Columns.Add("UpdateSource", typeof(string));
                    gridImportOrder.DataSource = newTable;
                }
                ImportOrder.Columns[0].Caption = "Order No";
                ImportOrder.Columns[1].Caption = "Part No";
                ImportOrder.Columns[2].Caption = "Quantity";
                ImportOrder.Columns[2].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                ImportOrder.Columns[2].DisplayFormat.FormatString = "{0:n}";
                ImportOrder.Columns[3].Caption = "Price";
                ImportOrder.Columns[4].Caption = "Total Cost";
                ImportOrder.Columns[4].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                ImportOrder.Columns[4].DisplayFormat.FormatString = "{0:n}";
                ImportOrder.Columns[5].Caption = "Currency";
                ImportOrder.Columns[6].Caption = "Invoice";
                ImportOrder.Columns[7].Caption = "Kobai";
                ImportOrder.Columns[8].Caption = "Delivery Date";
                ImportOrder.Columns[9].Caption = "Month";
                ImportOrder.Columns[10].Caption = "Supplier ID";
                ImportOrder.Columns[11].Caption = "Supplier Name";
                ImportOrder.Columns[12].Caption = "Source";
                ImportOrder.BestFitColumns();
            }
        }


        private void LoadSummary()
        {   
            if (string.IsNullOrEmpty(this.yearText.Text.Trim()))
            {
                MessageBox.Show("Please enter the year to view report");
            }
            else
            {
                SupplierSummary.Columns.Clear();
                SupplierSummary.Bands.Clear();
                gridSupplierSummary.DataSource = null;

                SupplierSummary.Bands.AddBand("Update Source");
                SupplierSummary.Bands.AddBand("Supplier");
                SupplierSummary.Bands[1].Children.AddBand("Supplier ID");
                SupplierSummary.Bands[1].Children.AddBand("Supplier Name");
                SupplierSummary.Bands.AddBand("Jan-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[2].Children.AddBand("Invoice");
                SupplierSummary.Bands[2].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[2].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Feb-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[3].Children.AddBand("Invoice");
                SupplierSummary.Bands[3].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[3].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Mar-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[4].Children.AddBand("Invoice");
                SupplierSummary.Bands[4].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[4].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Apr-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[5].Children.AddBand("Invoice");
                SupplierSummary.Bands[5].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[5].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("May-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[6].Children.AddBand("Invoice");
                SupplierSummary.Bands[6].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[6].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Jun-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[7].Children.AddBand("Invoice");
                SupplierSummary.Bands[7].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[7].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Jul-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[8].Children.AddBand("Invoice");
                SupplierSummary.Bands[8].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[8].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Aug-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[9].Children.AddBand("Invoice");
                SupplierSummary.Bands[9].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[9].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Sep-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[10].Children.AddBand("Invoice");
                SupplierSummary.Bands[10].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[10].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Oct-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[11].Children.AddBand("Invoice");
                SupplierSummary.Bands[11].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[11].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Nov-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[12].Children.AddBand("Invoice");
                SupplierSummary.Bands[12].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[12].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Dec-" + this.yearText.Text.Trim());
                SupplierSummary.Bands[13].Children.AddBand("Invoice");
                SupplierSummary.Bands[13].Children.AddBand("Not Invoice");
                SupplierSummary.Bands[13].Children.AddBand("Kobai (USD)");
                SupplierSummary.Bands.AddBand("Currency");


                DataSet sourceDataSet = new DataSet();
                SQLManagement mySQLManagement = new SQLManagement();
                mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
                string sql = "SELECT [UPDATESOURCE], [SUPPLIERID], [SupplierName],"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '01') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS JAN_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '01') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS JAN_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '01') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS JAN_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '02') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS FEB_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '02') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS FEB_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '02') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS FEB_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '03') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS MAR_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '03') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS MAR_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '03') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS MAR_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '04') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS APR_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '04') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS APR_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '04') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS ARP_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '05') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS MAY_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '05') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS MAY_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '05') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS MAY_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '06') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS JUN_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '06') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS JUN_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '06') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS JUN_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '07') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS JUL_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '07') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS JUL_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '07') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS JUL_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '08') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS AUG_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '08') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS AUG_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '08') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS AUG_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '09') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS SEP_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '09') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS SEP_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '09') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS SEP_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '10') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS OCT_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '10') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS OCT_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '10') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS OCT_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '11') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS NOV_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '11') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS NOV_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '11') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS NOV_KOBAI,"
                    + " SUM(CASE WHEN ([INVOICE] = 1) AND (SUBSTRING([MONTH], 6, 2) = '12') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS DEC_INVOICE,"
                    + " SUM(CASE WHEN ([INVOICE] = 0) AND (SUBSTRING([MONTH], 6, 2) = '12') AND ([KOBAI] = 0) THEN [TOTALCOST] ELSE 0 END) AS DEC_NOTINVOICE,"
                    + " SUM(CASE WHEN ((SUBSTRING([MONTH], 6, 2) = '12') AND ([KOBAI] = 1)) THEN [TOTALCOST] ELSE 0 END) AS DEC_KOBAI,"
                    + " ISNULL([CURRENCY], 'USD')"
                    + " FROM [VArringGoodsPlan2]"
                    + " WHERE SUBSTRING([MONTH], 1, 4) = '" + this.yearText.Text.Trim() + "'"
                    + " GROUP BY [UPDATESOURCE], [SUPPLIERID], [SupplierName], [CURRENCY]"
                    + " ORDER BY [SUPPLIERID]";
                sourceDataSet = mySQLManagement.SQL_Query(sql);
                if (sourceDataSet != null)
                {   
                    gridSupplierSummary.DataSource = sourceDataSet.Tables[0];
                }
                else
                {
                    MessageBox.Show("There is no data for Arriving goods plan");
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("UPDATESOURCE", typeof(string));
                    newTable.Columns.Add("SUPPLIERID", typeof(string));
                    newTable.Columns.Add("SupplierName", typeof(string));
                    newTable.Columns.Add("JAN_INVOICE", typeof(float));
                    newTable.Columns.Add("JAN_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("JAN_KOBAI", typeof(float));
                    newTable.Columns.Add("FEB_INVOICE", typeof(float));
                    newTable.Columns.Add("FEB_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("FEB_KOBAI", typeof(float));
                    newTable.Columns.Add("MAR_INVOICE", typeof(float));
                    newTable.Columns.Add("MAR_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("MAR_KOBAI", typeof(float));
                    newTable.Columns.Add("APR_INVOICE", typeof(float));
                    newTable.Columns.Add("APR_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("APR_KOBAI", typeof(float));
                    newTable.Columns.Add("MAY_INVOICE", typeof(float));
                    newTable.Columns.Add("MAY_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("MAY_KOBAI", typeof(float));
                    newTable.Columns.Add("JUN_INVOICE", typeof(float));
                    newTable.Columns.Add("JUN_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("JUN_KOBAI", typeof(float));
                    newTable.Columns.Add("JUL_INVOICE", typeof(float));
                    newTable.Columns.Add("JUL_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("JUL_KOBAI", typeof(float));
                    newTable.Columns.Add("AUG_INVOICE", typeof(float));
                    newTable.Columns.Add("AUG_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("AUG_KOBAI", typeof(float));
                    newTable.Columns.Add("SEP_INVOICE", typeof(float));
                    newTable.Columns.Add("SEP_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("SEP_KOBAI", typeof(float));
                    newTable.Columns.Add("OCT_INVOICE", typeof(float));
                    newTable.Columns.Add("OCT_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("OCT_KOBAI", typeof(float));
                    newTable.Columns.Add("NOV_INVOICE", typeof(float));
                    newTable.Columns.Add("NOV_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("NOV_KOBAI", typeof(float));
                    newTable.Columns.Add("DEC_INVOICE", typeof(float));
                    newTable.Columns.Add("DEC_NOTINVOICE", typeof(float));
                    newTable.Columns.Add("DEC_KOBAI", typeof(float));
                    newTable.Columns.Add("CURRENCY", typeof(string));
                    gridSupplierSummary.DataSource = newTable;
                }

                try
                {                    
                    SupplierSummary.Columns[0].OwnerBand = SupplierSummary.Bands[0];
                    SupplierSummary.Columns[0].Width = 100;
                    SupplierSummary.Columns[1].OwnerBand = SupplierSummary.Bands[1].Children[0];
                    SupplierSummary.Columns[1].Width = 80;
                    SupplierSummary.Columns[2].OwnerBand = SupplierSummary.Bands[1].Children[1];
                    SupplierSummary.Columns[2].Width = 200;
                    
                    /// JAN
                    SupplierSummary.Columns[3].OwnerBand = SupplierSummary.Bands[2].Children[0];
                    SupplierSummary.Columns[3].Width = 100;
                    SupplierSummary.Columns[4].OwnerBand = SupplierSummary.Bands[2].Children[1];
                    SupplierSummary.Columns[4].Width = 100;
                    SupplierSummary.Columns[5].OwnerBand = SupplierSummary.Bands[2].Children[2];
                    SupplierSummary.Columns[5].Width = 100;
                    /// FEB
                    SupplierSummary.Columns[6].OwnerBand = SupplierSummary.Bands[3].Children[0];
                    SupplierSummary.Columns[6].Width = 100;
                    SupplierSummary.Columns[7].OwnerBand = SupplierSummary.Bands[3].Children[1];
                    SupplierSummary.Columns[7].Width = 100;
                    SupplierSummary.Columns[8].OwnerBand = SupplierSummary.Bands[3].Children[2];
                    SupplierSummary.Columns[8].Width = 100;
                    /// MAR
                    SupplierSummary.Columns[9].OwnerBand = SupplierSummary.Bands[4].Children[0];
                    SupplierSummary.Columns[9].Width = 100;
                    SupplierSummary.Columns[10].OwnerBand = SupplierSummary.Bands[4].Children[1];
                    SupplierSummary.Columns[10].Width = 100;
                    SupplierSummary.Columns[11].OwnerBand = SupplierSummary.Bands[4].Children[2];
                    SupplierSummary.Columns[11].Width = 100;
                    /// APR
                    SupplierSummary.Columns[12].OwnerBand = SupplierSummary.Bands[5].Children[0];
                    SupplierSummary.Columns[12].Width = 100;
                    SupplierSummary.Columns[13].OwnerBand = SupplierSummary.Bands[5].Children[1];
                    SupplierSummary.Columns[13].Width = 100;
                    SupplierSummary.Columns[14].OwnerBand = SupplierSummary.Bands[5].Children[2];
                    SupplierSummary.Columns[14].Width = 100;
                    /// MAY
                    SupplierSummary.Columns[15].OwnerBand = SupplierSummary.Bands[6].Children[0];
                    SupplierSummary.Columns[15].Width = 100;
                    SupplierSummary.Columns[16].OwnerBand = SupplierSummary.Bands[6].Children[1];
                    SupplierSummary.Columns[16].Width = 100;
                    SupplierSummary.Columns[17].OwnerBand = SupplierSummary.Bands[6].Children[2];
                    SupplierSummary.Columns[17].Width = 100;
                    /// JUN
                    SupplierSummary.Columns[18].OwnerBand = SupplierSummary.Bands[7].Children[0];
                    SupplierSummary.Columns[18].Width = 100;
                    SupplierSummary.Columns[19].OwnerBand = SupplierSummary.Bands[7].Children[1];
                    SupplierSummary.Columns[19].Width = 100;
                    SupplierSummary.Columns[20].OwnerBand = SupplierSummary.Bands[7].Children[2];
                    SupplierSummary.Columns[20].Width = 100;
                    // JUL
                    SupplierSummary.Columns[21].OwnerBand = SupplierSummary.Bands[8].Children[0];
                    SupplierSummary.Columns[21].Width = 100;
                    SupplierSummary.Columns[22].OwnerBand = SupplierSummary.Bands[8].Children[1];
                    SupplierSummary.Columns[22].Width = 100;
                    SupplierSummary.Columns[23].OwnerBand = SupplierSummary.Bands[8].Children[2];
                    SupplierSummary.Columns[23].Width = 100;
                    // AUG
                    SupplierSummary.Columns[24].OwnerBand = SupplierSummary.Bands[9].Children[0];
                    SupplierSummary.Columns[24].Width = 100;
                    SupplierSummary.Columns[25].OwnerBand = SupplierSummary.Bands[9].Children[1];
                    SupplierSummary.Columns[25].Width = 100;
                    SupplierSummary.Columns[26].OwnerBand = SupplierSummary.Bands[9].Children[2];
                    SupplierSummary.Columns[26].Width = 100;
                    // SEP
                    SupplierSummary.Columns[27].OwnerBand = SupplierSummary.Bands[10].Children[0];
                    SupplierSummary.Columns[27].Width = 100;
                    SupplierSummary.Columns[28].OwnerBand = SupplierSummary.Bands[10].Children[1];
                    SupplierSummary.Columns[28].Width = 100;
                    SupplierSummary.Columns[29].OwnerBand = SupplierSummary.Bands[10].Children[2];
                    SupplierSummary.Columns[29].Width = 100;
                    // OCT
                    SupplierSummary.Columns[30].OwnerBand = SupplierSummary.Bands[11].Children[0];
                    SupplierSummary.Columns[30].Width = 100;
                    SupplierSummary.Columns[31].OwnerBand = SupplierSummary.Bands[11].Children[1];
                    SupplierSummary.Columns[31].Width = 100;
                    SupplierSummary.Columns[32].OwnerBand = SupplierSummary.Bands[11].Children[2];
                    SupplierSummary.Columns[32].Width = 100;
                    // NOV
                    SupplierSummary.Columns[33].OwnerBand = SupplierSummary.Bands[12].Children[0];
                    SupplierSummary.Columns[33].Width = 100;
                    SupplierSummary.Columns[34].OwnerBand = SupplierSummary.Bands[12].Children[1];
                    SupplierSummary.Columns[34].Width = 100;
                    SupplierSummary.Columns[35].OwnerBand = SupplierSummary.Bands[12].Children[2];
                    SupplierSummary.Columns[35].Width = 100;
                    // DEC
                    SupplierSummary.Columns[36].OwnerBand = SupplierSummary.Bands[13].Children[0];
                    SupplierSummary.Columns[36].Width = 100;
                    SupplierSummary.Columns[37].OwnerBand = SupplierSummary.Bands[13].Children[1];
                    SupplierSummary.Columns[37].Width = 100;
                    SupplierSummary.Columns[38].OwnerBand = SupplierSummary.Bands[13].Children[2];
                    SupplierSummary.Columns[38].Width = 100;

                    SupplierSummary.Columns[39].OwnerBand = SupplierSummary.Bands[14];
                    
                    SupplierSummary.Columns[3].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[3].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[4].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[4].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[5].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[5].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[6].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[6].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[7].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[7].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[8].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[8].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[9].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[9].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[10].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[10].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[11].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[11].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[12].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[12].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[13].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[13].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[14].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[14].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[15].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[15].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[16].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[16].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[17].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[17].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[18].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[18].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[19].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[19].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[20].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[20].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[21].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[21].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[22].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[22].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[23].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[23].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[24].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[24].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[25].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[25].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[26].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[26].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[27].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[27].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[28].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[28].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[29].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[29].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[30].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[30].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[31].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[31].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[32].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[32].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[33].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[33].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[34].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[34].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[35].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[35].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[36].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[36].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[37].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[37].DisplayFormat.FormatString = "{0:n}";
                    SupplierSummary.Columns[38].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    SupplierSummary.Columns[38].DisplayFormat.FormatString = "{0:n}";
                    
                    //SupplierSummary.BestFitColumns();                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }        

        private void buttonSwitch_Click(object sender, EventArgs e)
        {
            switch (this.stat)
            {
                case 0:
                    //LoadSummary();
                    SupplierSummary.Columns.Clear();
                    SupplierSummary.Bands.Clear();
                    gridSupplierSummary.DataSource = null;                    
                    this.labelControl1.Text = "Arriving Goods Plan";
                    this.gridSupplierSummary.BringToFront();
                    this.buttonSwitch.Text = "<< Details";
                    this.stat = 1;
                    break;
                case 1:
                    //LoadDetail();
                    ImportOrder.Columns.Clear();
                    gridImportOrder.DataSource = null;
                    this.labelControl1.Text = "Import Order";
                    this.gridImportOrder.BringToFront();
                    this.buttonSwitch.Text = ">> Summary";
                    this.stat = 0;
                    break;
            }
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            switch (this.stat)
            {
                case 0:
                    LoadDetail();                    
                    break;
                case 1:
                    LoadSummary();                    
                    break;
            }
        }

        private void buttonExport_Click(object sender, EventArgs e)
        {
            Cursor currentCursor;
            switch (this.stat)
            {
                case 0:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "ImportOrder.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                ImportOrder.OptionsPrint.PrintDetails = true;
                                ImportOrder.OptionsPrint.ExpandAllDetails = true;
                                ImportOrder.OptionsPrint.AutoWidth = true;
                                ImportOrder.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;
                case 1:
                    currentCursor = Cursor.Current;
                    Cursor.Current = Cursors.WaitCursor;
                    try
                    {
                        SaveFileDialog saveFDlg = new SaveFileDialog();
                        saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                        saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                        saveFDlg.Title = "Save As";
                        //saveFDlg.CheckFileExists = true;
                        saveFDlg.FileName = "SupplierSummary.xlsx";
                        if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                        {
                            if (!string.IsNullOrEmpty(saveFDlg.FileName))
                            {
                                SupplierSummary.OptionsPrint.PrintDetails = true;
                                SupplierSummary.OptionsPrint.ExpandAllDetails = true;
                                SupplierSummary.OptionsPrint.AutoWidth = false;
                                SupplierSummary.ExportToXlsx(saveFDlg.FileName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Warning!");
                    }
                    finally
                    {
                        Cursor.Current = currentCursor;
                    }
                    break;
            }
        }
    }
}
